<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
<meta http-equiv="X-UA-Compatible" content="IE=edge, chrome=1" />
<meta name="renderer" content="webkit" />
<title>CodeMirror-Mybatis-sql高级提示</title>


<link rel="stylesheet" href="https://cdn.bootcdn.net/ajax/libs/twitter-bootstrap/4.3.0/css/bootstrap.min.css" />


<link rel="stylesheet" href="https://cdn.bootcdn.net/ajax/libs/codemirror/5.62.3/codemirror.min.css" />
<link rel="stylesheet" href="https://cdn.bootcdn.net/ajax/libs/codemirror/5.62.3/theme/dracula.css" />
<link rel="stylesheet" href="https://cdn.bootcdn.net/ajax/libs/codemirror/5.62.3/addon/hint/show-hint.css" />
<link rel="stylesheet" href="https://cdn.bootcdn.net/ajax/libs/codemirror/5.62.3/addon/display/fullscreen.css">
<link rel="stylesheet" href="https://cdn.bootcdn.net/ajax/libs/codemirror/5.62.3/addon/dialog/dialog.css">

<!-- 自定义实现css -->
<link rel="stylesheet" href="../lib/sqlquery.css" />

<script src="https://cdn.bootcdn.net/ajax/libs/jquery/3.2.1/jquery.min.js"></script>

<script src="https://cdn.bootcdn.net/ajax/libs/codemirror/5.62.3/codemirror.min.js"></script>
<script src="https://cdn.bootcdn.net/ajax/libs/codemirror/5.62.3/addon/hint/show-hint.js"></script>
<script src="https://cdn.bootcdn.net/ajax/libs/codemirror/5.62.3/addon/selection/selection-pointer.js"></script>
<script src="https://cdn.bootcdn.net/ajax/libs/codemirror/5.62.3/addon/display/autorefresh.js"></script>
<script src="https://cdn.bootcdn.net/ajax/libs/codemirror/5.62.3/addon/display/placeholder.js"></script>
<script src="https://cdn.bootcdn.net/ajax/libs/codemirror/5.62.3/addon/display/fullscreen.js"></script>
<script src="https://cdn.bootcdn.net/ajax/libs/codemirror/5.62.3/addon/edit/matchbrackets.js"></script>
<script src="https://cdn.bootcdn.net/ajax/libs/codemirror/5.62.3/addon/hint/anyword-hint.js"></script>
<script src="https://cdn.bootcdn.net/ajax/libs/codemirror/5.62.3/addon/edit/closetag.js"></script>
<script src="https://cdn.bootcdn.net/ajax/libs/codemirror/5.62.3/addon/edit/matchtags.js"></script>
<script src="https://cdn.bootcdn.net/ajax/libs/codemirror/5.62.3/addon/fold/xml-fold.js"></script>
<script src="https://cdn.bootcdn.net/ajax/libs/codemirror/5.62.3/addon/fold/comment-fold.js"></script>
<script src="https://cdn.bootcdn.net/ajax/libs/codemirror/5.62.3/addon/comment/continuecomment.js"></script>
<script src="https://cdn.bootcdn.net/ajax/libs/codemirror/5.62.3/addon/comment/comment.js"></script>
<script src="https://cdn.bootcdn.net/ajax/libs/codemirror/5.62.3/addon/dialog/dialog.js"></script>


<!-- 自定义实现JS-->
<script src="../lib/mybatis.js"></script>
<script src="../lib/mybatissql.js"></script>

<style>
.sqlexample{
    font-size: 10px;
    color: blue;
}
.sqlexample:hover{
   min-width: 210px;
   font-size: 16px;
   position: fixed;
   z-index: 10;
   background: #E5E5E5;
   border-radius: 5px;
   padding: 3px 10px;
}
.list-group-item {
  height: 70px;
}
.CodeMirror {
  font-size: 14px;
  height: 100%;
}
</style>
</head>
<body>

<div class="container" style="padding: 20px;">

<div class="row">
    <div class="col-md-12">
<div class="alert alert-warning" role="alert" style="color: red;">
 @基于CodeMirror5实现的mybatis+sql提示，不依赖其他库。
 <br>
 @实现了mybatis与sql的混合嵌套模式，使其编写自定义sql更加轻松。
 <br>
 @sql实现了更为友好的提示效果：模糊匹配、多模式、多SQL、特殊别名处理、展示更多属性。
</div>

<div style="height:550px;position: relative;border: 1px solid #D2D2D2;padding: 10px;">

   <div class="row">
    <div class="col-4">
   <div class="form-group">
    <label for="exampleSelect1">数据库</label>
    <select class="form-control" id="exampleSelect1" style=""></select>
  </div>
    </div>
    <div class="col-3">
     <div class="form-group">
     <label for="exampleSelect2">模式(schema)</label>
     <select class="form-control" id="exampleSelect2" style=""></select>
     </div>
    </div>
   <div class="col-2">
    <div class="form-group">
    <label for="exampleSelect3">多模式开关</label>
    <select class="form-control" id="exampleSelect3" style=""></select>
    </div>
    </div>
     <div class="col-3">
    <div class="form-group">
    <label for="exampleSelect3">库类型</label>
    <select class="form-control" id="exampleSelect4" style="" disabled></select>
    </div>
    </div>
  </div>
  
   <div class="form-group" style="height: 400px;position: relative;">
    <textarea id="sqlcontent" style="display: none;" ></textarea>
  </div>
</div>
    
    
 </div>
</div>


</div>


<script>
var selfObj={
       existTables:{}, //已查询的表
       existTableCols:{}, //已查询的表字段
       databaseId: null, //当前数据库id
       schemaTypes: null, //当前数据库的模式集合
       mainSchema: null, //当前数据库的主模式
       sqlEditor: null, //sql编辑器
       hintOptions: null, //提示框hint对象
};
$(function(){
    //-------------------演示数据----------------------------
    //已查过的表，缓存到前端
    selfObj.existTables=getAllTables();
    //前端组件测试数据
    exampleDataInit();
    
    //-------------------CodeMirror初始化----------------------------
    //注册CodeMirror自定义模式
    mybatisHandler(CodeMirror);
    mybatisHintHandler(CodeMirror)
    sqlqueryHandler(CodeMirror);
    sqlqueryHintHandler(CodeMirror);
    
    //创建编辑器
    createMybatisEditor(selfObj,"sqlcontent");
    //处理hint触发事件，做成按需动态查询
    keypressSqlEditor(selfObj, getColsOfSchema, getTablesOfSchema);
    
    var mapperId = "com.my.zl.testMapper";
    var mapperContext = "  <sql id=\"_sql\">\r\n"
        + "      select a.BB1 from TBL_DICT_DTL a\r\n"
        + "  </sql>\r\n"
        + "  <select id=\"selectTestSql\" parameterType=\"\" >\r\n"
        + "      <include refid=\"_sql\" ></include>\r\n"
        + "      LEFT JOIN public.TBL_DICT t1 on\r\n"
        + "      LEFT JOIN test.TBL_TEST T2 on \r\n"
        + "    <where>\r\n"
        + "       <if test=\"AA1=='' and X=='' \">\r\n"
        + "       </if>  \r\n"
        + "    </where>\r\n"
        + "  </select>\r\n\r\n"
        + "  <insert id=\"\">\r\n"
        + "    INSERT INTO TBL_DICT (\r\n"
        + "     \r\n"
        + "    ) VALUES (\r\n"
        + "    \r\n"
        + "    )\r\n"
        + "  </insert>\r\n"
        ;
    //首次初始化，拼好了外层mapper主结构(也可以自行按需要处理)
    initValueMybatisEditor(selfObj.sqlEditor, mapperId, mapperContext);
});

/**
 * 演示数据
 */
function exampleDataInit(){
    //数据库信息（从接口获取）
    var databaselist=[{id:"1",type:"postgres",title:"postgres-127.0.0.1"}
        ,{id:"2",type:"mysql",title:"mysql-127.0.0.2"}
    ];
    //schema信息（从接口获取）
    var schemaMap={"1":["public","test","test2"], "2":["book","test"]};
    var databaseMap={};
    $.each(databaselist,function(i,item){
         $('#exampleSelect1').append('<option value="'+item.id+'">'+item.title+'</option>');
         databaseMap[item.id]=item;
    });
    //当前schema集合,true表示选择该库作为主库，主库默认可以不带库名
    $('#exampleSelect1').change(function() {
      selfObj.databaseId = $(this).val();
      selfObj.schemaTypes = {};
      $('#exampleSelect2').empty();
      $.each(schemaMap[selfObj.databaseId],function(i,item){
          $('#exampleSelect2').append('<option value="'+item+'">'+item+'</option>');
          selfObj.schemaTypes[item]=false;
      });
      $('#exampleSelect2').change();
      $('#exampleSelect4').val(databaseMap[selfObj.databaseId].type);
      if(selfObj.sqlEditor){
          //修改schema集合
          selfObj.hintOptions["schemaTypes"]=selfObj.schemaTypes;
          //修改sql类型
          var mappingDbType={"postgres": "text/x-pgsql", "mysql": "text/x-mysql"};
          selfObj.sqlEditor.setOption("sqlMode", mappingDbType[databaseMap[selfObj.databaseId].type]);
      }
    });
    $('#exampleSelect2').change(function() {
         selfObj.mainSchema = $(this).val();
         $.each(selfObj.schemaTypes,function(k,v){
             selfObj.schemaTypes[k]=(selfObj.mainSchema===k);
             $('#exampleSelect2').find("option[value='"+k+"']").text(k+(selfObj.existTables[selfObj.databaseId+"-"+k]?"（已加载）":"（未加载）"));
         });
    });
    $('#exampleSelect1').change();
    
    var openSchemaMap={"1":"开启","0":"关闭"};
    $.each(openSchemaMap,function(k,v){
         $('#exampleSelect3').append('<option value="'+k+'">'+v+'</option>');
    });
    $('#exampleSelect3').change(function() {
        if(selfObj.hintOptions){
           selfObj.hintOptions["schemaOpen"]= $(this).val()!=="0";
        }
    });
    $('#exampleSelect3').val("1");
    
    var dbTypeMap={"postgres":"postgreSQL","mysql":"Mysql"};
    $.each(dbTypeMap,function(k,v){
         $('#exampleSelect4').append('<option value="'+k+'">'+v+'</option>');
    });
}

/**
 * 后台接口：查询所有表（不含字段）
 * 如果表数量不多，进入页面时一次性加载所有表（不含字段）,反之最好实时查询当前schema
 */
function getAllTables(databaseId){
    //支持跨库 {"public.TBL_DICT":"表1"} 或 不跨库 {"TBL_DICT":"表1"}
    var testdata = {
            "1": {"public.TBL_DICT":"表1", "public.TBL_DICT_DTL":"表2", "test.TBL_TEST":"表3"},
            "2": {"book.TBL_DICT":"表A", "book.TBL_DICT_DTL":"表B", "test.TBL_TEST":"表C"},
            //登记schema，方便判断已查过的，没查过的
            "1-public": true,
            "1-test": true,
            "1-test2": false,//未加载
            "2-book": true,
            "2-test": true,
    };
    console.log("获取数据库信息>>",testdata);
    return testdata;
}

/**
 * [可以不实现]后台接口：查询生效schema的表
 * queryVal={databaseId: "111",schemaType: "public"]}
 */
function getTablesOfSchema(selfObj,queryVal,callbackHint){
    //支持跨库 {"public.TBL_DICT":"表1"} 或 不跨库 {"TBL_DICT":"表1"}
    var testdata = {
            "1-test2": {"test2.TBL_TEST_ORDER":"表3X"},
    };
    var key=queryVal["databaseId"]+"-"+queryVal["schemaType"];
    var data=testdata[key];
    console.log("实时查询Schema>>",key,data);
    //接口返回处理完成执行回调
    callbackHint(data);
    
    $('#exampleSelect2').change();
}

/**
 * [必须实现]后台接口：查询生效的表的字段
 * queryVal={databaseId: "111",tableNames: ["public.XXXX1","public.XXXX2"]}
 */
function getColsOfSchema(selfObj,queryVal,callbackHint){
    //表字段合集，根据条件动态查询接口返回，因为所有表字段的数量可能达到数万+++，不可能一次性存在前端
    var sqlTables={};
    
    var testdata = {
            "1": {"public.TBL_DICT":[["AA1","AA2","AA3"],["AA1标题AA1标题AA1标题AA1标题AA1标题","AA2标题","AA3标题"],["VARCHAR(50)","VARCHAR(60)","TIMESTAMP"],["主键：是    非空：是<br>分区：是<br>详情详情详情详情1详情详情详情详情1详情详情详情详情1详情详情详情详情1详情详情详情详情1","","详情详情详情详情3"]]
               ,"public.TBL_DICT_DTL":[["BBXXXX1","BB2","BB3"],["BB1XXXX标题","BB2标题","BB3标题"],["VARCHAR(50)","VARCHAR(100)","TIMESTAMP"]]
               ,"test.TBL_TEST":[["XX1","XX2","XX3"],["XX1标题","XX2标题","XX3标题"]]
               ,"test2.TBL_TEST_ORDER":[["CC1","CC2","CC3"],["CC1标题","CC2标题","CC3标题"]]
            },
            "2": {"book.TBL_DICT":[["AA1","AA2","AA3"],["AA1标题","AA2标题","AA3标题"],["CHAR(50)","CHAR(60)","TIMESTAMP"],["11","22","33"]]
               ,"book.TBL_DICT_DTL":[["BB1","BB2","BB3"],["BB1标题","BB2标题","BB3标题"]]
               ,"test.TBL_TEST":[["XX1","XX2","XX3"],["XX1标题","XX2标题","XX3标题"]]
            },
    };
   
    var t=testdata[queryVal["databaseId"]];
    $.each(queryVal["tableNames"],function(i,item){
        $.each(t,function(k,v){
            if(item.toLowerCase()===k.toLowerCase()){
                sqlTables[k]=v;
                return;
            }
        });
    });
    console.log("实时查询字段>>",queryVal["tableNames"],t);
    //接口返回处理完成执行回调
    callbackHint(sqlTables);
}


</script>
   
</body>
</html>